"""person"""
import math
from datetime import datetime
from flask import g
from psycopg2 import extras
import pytz



def index(project_id, status):
    """人员列表"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        base_query = """
            SELECT 
                p.id, p.name, p.phone, p.work_org, p.id_card, pr.room_id, ppr.verify_code, ppr.role_id, ppr.status
            FROM 
                persons p 
            JOIN 
                project_person_relation ppr 
                ON p.id = ppr.person_id AND ppr.deleted_at IS NULL
            JOIN projects pr 
                ON ppr.project_id = pr.id AND pr.deleted_at IS NULL
            WHERE 
                p.deleted_at IS NULL AND ppr.project_id = %s
        """

        if status:
            base_query += " AND ppr.status = %s ORDER BY ppr.created_at DESC"
        cursor.execute(base_query, (project_id, status) if status else (project_id,))
        return cursor.fetchall()


def create(data):
    """创建人员"""
    with g.db_conn.cursor() as cursor:
        current_timestamp = int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp())
        person_base_query = "INSERT INTO persons (name, id_card, phone, created_at) VALUES (%s, %s, %s, %s) RETURNING id"
        person_params = [data['name'], data['id_card'], data.get('phone'), current_timestamp]
        cursor.execute(person_base_query, person_params)
        return cursor.fetchone()[0]


def exists(id_card):
    """检查人员是否存在"""
    with g.db_conn.cursor() as cursor:
        query = "SELECT EXISTS (SELECT 1 FROM persons WHERE id_card = %s AND deleted_at IS NULL)"
        cursor.execute(query, [id_card])
        return cursor.fetchone()[0]


def update(data, person_id):
    """更新人员"""
    with g.db_conn.cursor() as cursor:
        current_timestamp = int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp())
        set_data = []
        params = []

        set_data.append("updated_at = %s")
        params.append(current_timestamp)

        if 'name' in data:
            set_data.append("name = %s")
            params.append(data['name'])

        if 'id_card' in data:
            set_data.append("id_card = %s")
            params.append(data['id_card'])

        if 'phone' in data:
            set_data.append("phone = %s")
            params.append(data['phone'])

        set_clause = ", ".join(set_data)

        update_query = f"""UPDATE persons SET {set_clause}  WHERE id = %s RETURNING id"""
        params.append(person_id)
        cursor.execute(update_query, params)
        g.db_conn.commit()
        return cursor.fetchone()[0]


def undone():
    """待签到 ｜ 已签到"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        query = """
            SELECT 
                p.id, p.name, pr.project_code, ppr.status, ppr.verify_code, p.id_card, pr.room_id 
            FROM 
                persons p
            JOIN 
                project_person_relation ppr 
                ON p.id = ppr.person_id AND ppr.deleted_at IS NULL
            JOIN 
                projects pr 
                ON ppr.project_id = pr.id AND pr.deleted_at IS NULL
            WHERE 
                p.deleted_at IS NULL AND ppr.status in (1, 2) 
                AND role_id in (1, 2) ORDER BY p.id ASC
        """
        cursor.execute(query)
        return cursor.fetchall()


def find_by_id_card(id_card):
    """根据身份证号查询人员"""
    with g.db_conn.cursor() as cursor:
        query = """SELECT id FROM persons WHERE id_card = %s AND deleted_at IS NULL"""
        cursor.execute(query, [id_card])
        return cursor.fetchone()[0]

def find_by_id(relation_id):
    """根据ID查询人员"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        query = """
            SELECT 
                p.name, p.id_card, p.phone, p.work_org, ppr.identity 
            FROM 
                persons p
            LEFT JOIN 
                project_person_relation ppr ON p.id = ppr.person_id
            WHERE ppr.id = %s AND p.deleted_at IS NULL AND ppr.deleted_at IS NULL"""
        cursor.execute(query, (relation_id,))
        return cursor.fetchone()

def user_cabinet(page_number, page_size, keyword, sort_by, sort_order):
    """人员匹配柜子"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        query = """
            WITH p AS (
                SELECT 
                    p.name, p.id_card, p.face_pic, p.box_id, p.phone, p.work_org, p.id
                FROM 
                    persons p 
                JOIN 
                    project_person_relation ppr 
                    ON ppr.person_id = p.id
                WHERE
                    ppr.deleted_at IS NULL AND ppr.status = 2
            )
            SELECT 
                c.position, c.position_id, c.row, c.column, c.name cabinet_name, c.grid_no,
                c.space_status, c.access_status, c.lock_status, c.exception_status, c.openclose_status, 
                c.boxid, c.ip_address,
                p.name, p.id_card, p.phone, p.work_org
            FROM 
                cabinet c
            JOIN 
                p 
                ON p.box_id = c.id
        """

        params = []
        where_clause = ""
        if keyword:
            k = "%" + keyword + "%"
            where_clause = " WHERE p.name like %s OR p.id_card like %s OR p.phone like %s"
            params.extend([k, k, k])

        exec_query = f"{query}{where_clause} ORDER BY {sort_by} {sort_order} NULLS LAST LIMIT %s OFFSET %s"
        params.extend([page_size, (page_number - 1) * page_size])
        cursor.execute(exec_query, params)
        return cursor.fetchall()


def total(keyword=None):
    """ total """
    with g.db_conn.cursor() as cursor:
        base_query = """
            WITH p AS (
                SELECT 
                    p.name, p.id_card, p.face_pic, p.box_id, p.phone, p.work_org
                FROM 
                    persons p 
                JOIN 
                    project_person_relation ppr 
                    ON ppr.person_id = p.id AND ppr.deleted_at IS NULL AND ppr.status = 2 AND ppr.role_id != 3
            )
            SELECT 
                COUNT(*)
            FROM 
                cabinet c
            JOIN 
                p 
                ON p.box_id = c.id
        """
        if keyword:
            k = "%" + keyword + "%"
            base_query += " WHERE p.name like %s OR p.id_card like %s OR p.phone like %s"
            cursor.execute(base_query, [k, k, k])
        else:
            cursor.execute(base_query)
        return cursor.fetchone()[0]


def page_info(page_number, page_size, total_rows):
    """paginate info"""
    total_pages = math.ceil(total_rows / page_size)
    first_page = 1
    last_page = total_pages if total_pages > 0 else 1

    pre_page_uri = f"/api/user_cabinet?page_number={max(page_number - 1, first_page)}&page_size={page_size}" if page_number > first_page else None
    next_page_uri = f"/api/user_cabinet?page_number={min(page_number + 1, last_page)}&page_size={page_size}" if page_number < last_page else None

    return {
        'page_number': page_number,
        'page_size': page_size,
        'total': total_rows,
        'first_page': first_page,
        'last_page': last_page,
        'pre_page_uri': pre_page_uri,
        'next_page_uri': next_page_uri,
    }


def identity(account, password):
    """人员身份"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        query = "SELECT id FROM persons WHERE id_card = %s AND deleted_at IS NULL"
        cursor.execute(query, (account,))
        result = cursor.fetchone()
        if not result:
            return None, "账号无效"
        
        person_id = result['id']
        query = "SELECT id, project_id FROM project_person_relation WHERE person_id = %s AND identity = %s AND  deleted_at IS NULL"
        cursor.execute(query, (person_id, password))
        result = cursor.fetchone()
        if not result:
            return None, "密码错误"
        
        query = """SELECT
                        id, project_name, project_code, start_time, qrcode_base64, room_id,
                        TO_CHAR(TO_TIMESTAMP(start_time) AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') start_time_format 
                    FROM 
                        projects 
                    WHERE 
                        id = %s AND deleted_at IS NULL AND status != 2
                """
        
        cursor.execute(query, (result['project_id'],))
        project = cursor.fetchone()
        if not project:
            return None, "密码已失效"

        return project, None